package rak.r91.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Date;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import rak.r91.data.Item;
import rak.r91.data.ItemAction;
import rak.r91.data.ItemStore;
import rak.r91.data.Store;

public class JdbcStoreDao extends JdbcDaoSupport implements StoreDao{

	@Override
	public List<Store> getStores() {		
		return getJdbcTemplate().query("SELECT * FROM store", new storeMapper());
	}
	private static final class storeMapper implements RowMapper<Store> {
	    public Store mapRow(ResultSet rs, int rowNum) throws SQLException {
	    	Store store = new Store();
	    	store.setStoreID(rs.getInt("store"));
	    	store.setName(rs.getString("name"));
			return store;	    	
	    }
	}
	

	@Override
	public int itemInStore(Item item, Store store) {
		return getJdbcTemplate().queryForInt("SELECT count(*) FROM item_store WHERE item_fk=? AND store_fk=?", new Object[]{item.getItemID(),store.getStoreID()});
	}

	@Override
	public int updateItemStore(ItemStore itemStore) {
		return getJdbcTemplate().update("UPDATE item_store SET item_count=item_count + ? WHERE item_store=?", new Object[]{itemStore.getItemCount(),itemStore.getItemStoreID()});
	}

	@Override
	public int insertItemToStore(ItemStore itemStore) {
		return getJdbcTemplate().update("INSERT INTO item_store (store_fk, item_fk, item_count) values (?, ?, ?)", 
		        new Object[] {itemStore.getStore().getStoreID(), itemStore.getItem().getItemID(),itemStore.getItemCount()});
	}

	@Override
	public int registerItemAction(ItemAction ia) {
		System.out.println("employee "+ia.getEmployee());
		return getJdbcTemplate().update("INSERT INTO item_action (item_fk,store_from_fk,store_to_fk,action_date,item_count,action_price,action_note,created_by,created) values (?,?,?,?,?,?,?,?,?)", 
		        new Object[] {ia.getItem().getItemID(),ia.getStoreFrom().getStoreID(),ia.getStoreTo().getStoreID(),new Timestamp(new Date().getTime()),ia.getItemCount(),ia.getActionValue(),ia.getActionNote(),ia.getEmployee().getEmployeeId(),new Timestamp(new Date().getTime())});
	}

	@Override
	public int itemsInStores(Item item) {
		return getJdbcTemplate().queryForInt("SELECT SUM(item_count) FROM item_store WHERE item_fk = "+item.getItemID());
	}

	@Override
	public List<ItemStore> getStores(Item item) {
		return getJdbcTemplate().query("SELECT item_store,store_fk,item_count,name FROM item_store IT INNER JOIN store S on IT.store_fk=S.store WHERE IT.item_fk="+item.getItemID(), new ItemStoreMapper());
	}
	private static final class ItemStoreMapper implements RowMapper<ItemStore> {
	    public ItemStore mapRow(ResultSet rs, int rowNum) throws SQLException {
	    	ItemStore itemStore = new ItemStore();
	        Store store = new Store();
	        itemStore.setItemStoreID(rs.getInt("item_store"));
	        store.setStoreID(rs.getInt("store_fk"));
	        itemStore.setItemCount(rs.getInt("item_count"));
	        store.setName(rs.getString("name"));
	        itemStore.setStore(store);
	        return itemStore;
	    }
	}


	@Override
	public int removeItemFormStore(ItemStore itemStore) {
		return getJdbcTemplate().update("UPDATE item_store SET item_count=item_count - "+itemStore.getItemCount()+" WHERE item_store="+itemStore.getItemStoreID());
	}

}
